﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace DataAccess
{
    public class AppDatabase
    {

        public static string ConnectionString
        {
            get { return ConfigurationManager.ConnectionStrings["AppDatabase"].ConnectionString; }
        }

        #region Customers
        public DataSet GetCustomers(string CompanyName = "")
        {
            DataSet ds;
            //1. Crear Conexion
            using (SqlConnection cn = new SqlConnection(ConnectionString))
            {
                //1.1...y Abrir Conexion
                cn.Open();

                //2. Crear Sentencia de Comando
                SqlCommand cmd = cn.CreateCommand();
                //2.1. Sentencia
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = @"SELECT * 
                                FROM Customers
                                WHERE CompanyName LIKE @CompanyName
                                ORDER BY CompanyName";
                //2.2. Parametros
                // validar
                if (string.IsNullOrEmpty(CompanyName)) { CompanyName = "%"; }
                else { CompanyName += "%"; }
                // agregar parametro
                cmd.Parameters.Add(
    new SqlParameter("@CompanyName", SqlDbType.NVarChar, 40) { Value = CompanyName }
                    );

                //3. Ejecutar y Obtener Datos
                ds = new DataSet();
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    ds.Load(dr, LoadOption.OverwriteChanges,
                          new string[] { "Customers" });
                }
                //4. Cerrar Conexion
            } //cn.Close();
            return ds;
        }

        public DataSet CustomersGet_SP(string CompanyName = "")
        {
            DataSet ds;
            //1. Crear Conexion
            using (SqlConnection cn = new SqlConnection(ConnectionString))
            {
                //1.1...y Abrir Conexion
                cn.Open();

                //2. Crear Sentencia de Comando
                SqlCommand cmd = cn.CreateCommand();
                //2.1. Sentencia
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "Customers_Get";
                //2.2. Parametros
                cmd.Parameters.Add(
    new SqlParameter("@CompanyName", SqlDbType.NVarChar, 40) { Value = CompanyName }
                    );

                //3. Ejecutar y Obtener Datos
                ds = new DataSet();
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    ds.Load(dr, LoadOption.OverwriteChanges,
                          new string[] { "Customers" });
                }
                //4. Cerrar Conexion
            } //cn.Close();
            return ds;
        }
        #endregion

        #region Separacion de Capas
        public void InsertarCategoriaProducto_Part1(string CategoryName, string ProductName)
        {
            ExecuteNonQuery("InsertarCategoriaProducto",
    new SqlParameter("@CategoryName", SqlDbType.NVarChar, 15) { Value = CategoryName },
    new SqlParameter("@ProductName", SqlDbType.NVarChar, 40) { Value = ProductName }
                );
        }

        public void ExecuteNonQuery(
                string ProcedureName, params SqlParameter[] Parameters)
        {
            // crea conexion
            using (SqlConnection cn = new SqlConnection(ConnectionString))
            {
                cn.Open();      // abre conexion

                SqlCommand cmd = cn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = ProcedureName;
                cmd.Parameters.Add(Parameters);
                cmd.ExecuteNonQuery(); // ejecuto
            } // end using
        }

        public object ExecuteScalar(
            string ProcedureName, params SqlParameter[] Parameters)
        {
            // crea conexion
            using (SqlConnection cn = new SqlConnection(ConnectionString))
            {
                cn.Open();      // abre conexion

                SqlCommand cmd = cn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = ProcedureName;
                cmd.Parameters.Add(Parameters);
                return cmd.ExecuteScalar(); // ejecuto
            } // end using
        }

        public DataSet ExecuteDataSet(
    string ProcedureName, params SqlParameter[] Parameters)
        {
            // crea conexion
            using (SqlConnection cn = new SqlConnection(ConnectionString))
            {
                cn.Open();      // abre conexion

                SqlCommand cmd = cn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = ProcedureName;
                cmd.Parameters.Add(Parameters);
                DataSet ds = new DataSet();
                ds.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges, new string[] { "" });
                return ds;
            } // end using
        }
        #endregion

        #region Transacciones
        public void InsertarCategoriaProducto_SP(string CategoryName, string ProductName)
        {
            // crea conexion
            using (SqlConnection cn = new SqlConnection(ConnectionString))
            {
                cn.Open();      // abre conexion

                SqlCommand cmd = cn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "InsertarCategoriaProducto";
                //2.2. Parametros
                cmd.Parameters.Add(
                    new SqlParameter[] {
        new SqlParameter("@CategoryName", SqlDbType.NVarChar, 15) { Value = CategoryName },
        new SqlParameter("@ProductName", SqlDbType.NVarChar, 40) { Value = ProductName }
                    });
                cmd.ExecuteNonQuery(); // ejecuto

            } // end using
        }

        public void InsertarCategoriaProducto(string CategoryName, string ProductName)
        {
            // crea conexion
            using (SqlConnection cn = new SqlConnection(ConnectionString))
            {
                cn.Open();      // abre conexion
                SqlTransaction trx = cn.BeginTransaction(); // inicia transaccion

                // 1. INSERTAR CATEGORIA
                string SqlText =
                    @"INSERT INTO Categories (CategoryName) VALUES (@CategoryName)";
                SqlCommand cmd = new SqlCommand(SqlText, cn, trx);
                cmd.Parameters.Add(
new SqlParameter("@CategoryName", SqlDbType.NVarChar, 15) { Value = CategoryName }
                );
                cmd.ExecuteNonQuery(); // ejecuto

                // 2. Capturar ID
                SqlText = "SELECT @@IDENTITY";
                cmd = new SqlCommand(SqlText, cn, trx);
                int CategoryID = Convert.ToInt32(cmd.ExecuteScalar()); // retorna fila 1, col 1

                // 3. INSERTAR PRODUCTO
                SqlText =
                    @"INSERT INTO Products (ProductName, CategoryID) 
                        VALUES (@ProductName, @CategoryID)";
                cmd = new SqlCommand(SqlText, cn, trx); // <= especificar transaccion
                cmd.Parameters.AddRange(
                    new SqlParameter[] { // <= requerido para varios parametros
new SqlParameter("@ProductName", SqlDbType.NVarChar, 40) { Value = ProductName },
new SqlParameter("@CategoryID", SqlDbType.Int) { Value = CategoryID }
                });
                cmd.ExecuteNonQuery(); // ejecuto


                // completa transaccion
                trx.Commit();
            } // end using
        }
        #endregion

        /// <summary>
        /// Crear Conexion y Abrir Conexion
        /// </summary>
        public void TestConnection()
        {
            using (SqlConnection cn = new SqlConnection(ConnectionString))
            { cn.Open(); }
        }
    }
}
